package com.fare.taluo.fetl.domain.repository.impl;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.fare.taluo.fetl.domain.base.EntityFetlHibernateRepository;
import com.fare.taluo.fetl.domain.entity.FreightMonitorCabinsEntity;
import com.fare.taluo.fetl.domain.entity.FreightMontiorEntity;
import com.fare.taluo.fetl.domain.repository.IFreightMontorRepository;
import com.fare.taluo.util.DateUtils;
import com.fare.taluo.util.StringAndDateAndFileUtil;
@Repository
public class FreightMontorRepositoryImpl extends EntityFetlHibernateRepository implements IFreightMontorRepository{
	public  List<FreightMontiorEntity> findAll(){
		return super.findAll(FreightMontiorEntity.class);
	}
	
	/**
	 * 根据航班号flightNumber、航班日期departDate、创建日期opDate、起飞城市三字码fromCode，到达城市三字码toCode,查找记录。
	*/	
	
	public List<FreightMontiorEntity> findByData(String flightNumber,String departDate,String opDate,String fromCode,String toCode,String airHbaseKey){
		
	//	System.out.println("++++++++++departDate:"+departDate);
	//	System.out.println("++++++++++opDate"+opDate);
		List<Object> params = new ArrayList<Object>();
		String hql = "from com.fare.taluo.fetl.domain.entity.FreightMontiorEntity entity  where type=1 ";
		if(StringAndDateAndFileUtil.checkString(flightNumber)){
			hql += " and entity.flightNo= ?";
			params.add(flightNumber);
		}
		if(StringAndDateAndFileUtil.checkString(airHbaseKey)){
			hql += " and entity.grabChannelKey= ?";
			params.add(airHbaseKey);
		}
		if(StringAndDateAndFileUtil.checkString(fromCode)){
			hql += " and entity.fromCity= ?";
			params.add(fromCode);
		}
		if(StringAndDateAndFileUtil.checkString(toCode)){
			hql += " and entity.toCity= ?";
			params.add(toCode);
		}
		if(StringAndDateAndFileUtil.checkString(departDate) && StringAndDateAndFileUtil.checkString(opDate)){
			hql += " and entity.grabDate between str_to_date(?,'%Y-%m-%d') and str_to_date(?,'%Y-%m-%d')  ";
			params.add(opDate);
			params.add(departDate);
		}
		if(StringAndDateAndFileUtil.checkString(departDate) ){
			hql += " and entity.flightDate = str_to_date(?,'%Y-%m-%d')  ";
			params.add(departDate);
		}
		
		hql += " order by entity.grabDate ,entity.grabNum ";
		
		//Query query = getSession().createQuery(hql);
	//	System.out.println("+++++++hql:"+hql);
		Session session =getSessionFactory1().openSession();
	//	Session session  =	super.getHibernateTemplate1().getSessionFactory().openSession();
		List<FreightMontiorEntity> list = null;
		try {
			Query query = session.createQuery(hql);
			for (int i = 0; i < params.size(); i++) {
			query = query.setParameter(i, params.get(i));
			}
		//	 list = query.setMaxResults(24).list();
			 list = query.list();
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		/*for (int i = 0; i < params.size(); i++) {
			query = query.setParameter(i, params.get(i));
		}
//		List<FreightMontiorEntity> lists=super.find(hql.toString(),params.toArray(), FreightMontiorEntity.class);
		return query.list();*/
		return list;
	}
	/**
	 * 按条件查询并按抓取时间降序排序
	 */
	@Transactional
	public List<FreightMontiorEntity> findByConditions(int type, String fromCity,String toCity,String grabChannelKey,String flightNo,String flightDate){
		
		List<Object> params = new ArrayList<Object>();

		String hql = "from com.fare.taluo.fetl.domain.entity.FreightMontiorEntity entity  where type="+type+" ";

		if(StringAndDateAndFileUtil.checkString(fromCity)){
			hql += " and entity.fromCity= ?";
			params.add(fromCity);
		}
		if(StringAndDateAndFileUtil.checkString(toCity)){
			hql += " and entity.toCity= ?";
			params.add(toCity);
		}
		if(StringAndDateAndFileUtil.checkString(grabChannelKey)){
			hql += " and entity.grabChannelKey= ?";
			params.add(grabChannelKey);
		}
		if(StringAndDateAndFileUtil.checkString(flightNo)){
			hql += " and entity.flightNo= ?";
			params.add(flightNo);
		}
		if(StringAndDateAndFileUtil.checkString(flightDate)){
			hql += " and entity.flightDate= ?";
			try {
				params.add(DateUtils.parseDate(flightDate, "yyyy-MM-dd"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}

		hql += " order by entity.grabDate   ";
		Session session =getSessionFactory1().openSession();
		//Session session  =	super.getHibernateTemplate1().getSessionFactory().openSession();
		Query query = session.createQuery(hql);
		List<FreightMontiorEntity> list = null;
		try {
			query = session.createQuery(hql);
			for (int i = 0; i < params.size(); i++) {
			query = query.setParameter(i, params.get(i));
			}
			 list = query.list();
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		
		return list;
	}
	
	@Override
	public List<FreightMontiorEntity> findForLowPrice(String opDate,String flightDate,String flightNo,int size) throws Exception {
		List<Object> params = new ArrayList<Object>();
		DateFormat fmt =new SimpleDateFormat("yyyy-MM-dd");
		Date opDateTime =fmt.parse(opDate);
		Date flightDateType =fmt.parse(flightDate);
		String hql = "from com.fare.taluo.fetl.domain.entity.FreightMontiorEntity entity where  1=1 ";
		if(StringAndDateAndFileUtil.checkString(opDate)){
			hql += " and DATE_FORMAT(entity.grabDate ,'%Y-%m-%d') >= ? "; //抓取日期大于或等于创建时间
			params.add(opDate);
		}
		if(StringAndDateAndFileUtil.checkString(flightDate)){
			hql += " and DATE_FORMAT(entity.flightDate,'%Y-%m-%d') = DATE_FORMAT(?,'%Y-%m-%d')  "; //航班日期
			params.add(flightDateType);
		}
		if(StringAndDateAndFileUtil.checkString(flightNo)){
			hql += " and entity.flightNo= ?"; //航班号
			params.add(flightNo);
		}
	//	hql += " and entity.cabinNum > 0 ";
		hql += " order by entity.grabDate desc ";
		Session session =getSessionFactory1().openSession();
		//Session session  =	super.getHibernateTemplate1().getSessionFactory().openSession();
		List<FreightMontiorEntity> list = null;
		try {
			Query query = session.createQuery(hql);
			for (int i = 0; i < params.size(); i++) {
			query = query.setParameter(i, params.get(i));
			}
			 list = query.setMaxResults(size).list();
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		
		return list;
	}
	//仓位变化表查找
	@Override
	public List<FreightMontiorEntity> findByDataForm(String flightNumber,
			String departDate, String opDate, String fromCode, String toCode,
			String airHbaseKey) {


		List<Object> params = new ArrayList<Object>();
		String hql = "from com.fare.taluo.fetl.domain.entity.FreightMontiorEntity entity  where type=1  ";
		if(StringAndDateAndFileUtil.checkString(flightNumber)){
			hql += " and entity.flightNo= ?";
			params.add(flightNumber);
		}
		if(StringAndDateAndFileUtil.checkString(airHbaseKey)){
			hql += " and entity.grabChannelKey= ?";
			params.add(airHbaseKey);
		}
		if(StringAndDateAndFileUtil.checkString(fromCode)){
			hql += " and entity.fromCity= ?";
			params.add(fromCode);
		}
		if(StringAndDateAndFileUtil.checkString(toCode)){
			hql += " and entity.toCity= ?";
			params.add(toCode);
		}
		if(StringAndDateAndFileUtil.checkString(departDate) && StringAndDateAndFileUtil.checkString(opDate)){
			hql += " and entity.grabDate between str_to_date(?,'%Y-%m-%d') and str_to_date(?,'%Y-%m-%d')  ";
			params.add(opDate);
			params.add(departDate);
		}
		if(StringAndDateAndFileUtil.checkString(departDate) ){
			hql += " and entity.flightDate = str_to_date(?,'%Y-%m-%d')  ";
			params.add(departDate);
		}
		
		hql += " order by entity.grabDate,entity.grabNum ";
		
		//Query query = getSession().createQuery(hql);
	//	System.out.println("+++++++hql:"+hql);
		Session session =getSessionFactory1().openSession();
		//Session session  =	super.getHibernateTemplate1().getSessionFactory().openSession();
		List<FreightMontiorEntity> list = null;
		try {
			Query query = session.createQuery(hql);
			for (int i = 0; i < params.size(); i++) {
			query = query.setParameter(i, params.get(i));
			}
			 list = query.list();
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		/*for (int i = 0; i < params.size(); i++) {
			query = query.setParameter(i, params.get(i));
		}
//		List<FreightMontiorEntity> lists=super.find(hql.toString(),params.toArray(), FreightMontiorEntity.class);
		return query.list();*/
		return list;
	}

	@Override
	public List<FreightMontiorEntity> findMaxGrabNum(String maxDate,
			String flightNos) {
		List<Object> params = new ArrayList<Object>();
		String hql = "from com.fare.taluo.fetl.domain.entity.FreightMontiorEntity entity  where 1=1 ";
		if(StringAndDateAndFileUtil.checkString(flightNos)){
			hql += " and entity.flightNo= ?";
			params.add(flightNos);
		}
		if(StringAndDateAndFileUtil.checkString(maxDate)){
			hql += " and DATE_FORMAT(entity.grabDate ,'%Y-%m-%d %H:%M:%S') > ? ";
			params.add(maxDate);
			
		}
		
		hql += " order by entity.grabDate desc,entity.grabNum ";
		Session session =getSessionFactory1().openSession();
		//Session session  =	super.getHibernateTemplate1().getSessionFactory().openSession();
		List<FreightMontiorEntity> list = null;
		try {
			Query query = session.createQuery(hql);
			for (int i = 0; i < params.size(); i++) {
			query = query.setParameter(i, params.get(i));
			}
			 list = query.list();
		} catch (Exception e) {
			e.printStackTrace();	
		}finally{
			session.close();
		}
		return list;
	}

	@Override
	public List<FreightMonitorCabinsEntity> findById(String id) {
		
		try {
			List<Object> params = new ArrayList<Object>();
			String hql = "from FreightMonitorCabinsEntity entity  where 1=1 ";
			if(StringAndDateAndFileUtil.checkString(id)){
				hql += " and entity.monitorId= ?";
				params.add(id);
			}
			Session session =getSessionFactory1().openSession();
			//Session session  =	super.getHibernateTemplate1().getSessionFactory().openSession();
			List<FreightMonitorCabinsEntity> list = null;
			try {
				Query query = session.createQuery(hql);
				for (int i = 0; i < params.size(); i++) {
				query = query.setParameter(i, params.get(i));
				}
				 list = query.list();
			} catch (Exception e) {
				e.printStackTrace();	
			}finally{
				session.close();
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
		
	}
	
}
